const pool = require("./db.js");

class SalaryDao {
    // 查询员工有工资记录的所有年月份
    async querySalaryMonthsByUserId(userId) {
    try {
        const rows = await pool.query(
            `SELECT DISTINCT DATE_FORMAT(salary_date, '%Y-%m') AS salary_month
             FROM salary 
             WHERE salary_user_id = ?`, 
            [userId]
        );
        return rows.map(row => row.salary_month);
    } catch (err) {
        console.error("Error getting Salary Months:", err);
        throw err;
    }
}
    
    // 查询基本工资
    async queryBaseSalary(userId) {
    try {
        const rows = await pool.query(
            `SELECT p.pos_salary 
             FROM user u 
             JOIN position p ON u.user_position_id = p.pos_id 
             WHERE u.user_id = ?`, 
            [userId]
        );
        return rows[0]?.pos_salary || 0;
    } catch (err) {
        console.error("Error getting BaseSalary:", err);
        throw err;
    }
}
    // 查询考勤金额
    async queryAttendanceMoney(userId, month) {
    try {
        const rows = await pool.query(
            `SELECT atd_money 
             FROM attendance 
             WHERE atd_user_id = ? AND DATE_FORMAT(atd_date, '%Y-%m') = ?`, 
            [userId, month]
        );
        return rows[0]?.atd_money || 0;
    } catch (err) {
        console.error("Error getting Attendance Money:", err);
        throw err;
    }
}
    // 查询津贴数额
    async queryAllowanceMoney(userId, month) {
    try {
        const rows = await pool.query(
            `SELECT allw_money 
             FROM allowance 
             WHERE allw_user_id = ? AND DATE_FORMAT(allw_date, '%Y-%m') = ?`, 
            [userId, month]
        );
        return rows[0]?.allw_money || 0;
    } catch (err) {
        console.error("Error getting Allowance Money:", err);
        throw err;
    }
}
    // 查询总工资
    async queryTotalSalary(userId, month) {
    try {
        const rows = await pool.query(
            `SELECT salary_total 
             FROM salary 
             WHERE salary_user_id = ? AND DATE_FORMAT(salary_date, '%Y-%m') = ?`, 
            [userId, month]
        );
        return rows[0]?.salary_total || 0;
    } catch (err) {
        console.error("Error getting Total Salary:", err);
        throw err;
    }
}
// 查询员工本年度的工资总和
async queryAnnualSummary(userId) {
    try {
        const rows = await pool.query(
            `SELECT SUM(salary_total) AS totalSalary 
             FROM salary 
             WHERE salary_user_id = ? AND YEAR(salary_date) = YEAR(CURDATE())`, 
            [userId]
        );
        return rows[0] || { totalSalary: 0 };
    } catch (err) {
        console.error("Error getting Annual Summary:", err);
        throw err;
    }
}
}

module.exports = new SalaryDao();